# python/data_import.py

import pandas as pd
from sqlalchemy import create_engine
from .model.car_sales import CarSales
from .extensions import db
from . import create_app


def import_excel_to_mysql(file_path):
    app = create_app()
    with app.app_context():
        # 读取Excel文件
        df = pd.read_excel(file_path, sheet_name='car_sales')

        # 数据清洗和转换
        df = df.rename(columns={
            '年份': 'year',
            '月份': 'month',
            '排名': 'ranking',
            '车型': 'model',
            '厂商': 'manufacturer',
            '销量': 'sales_volume',
            '售价（万元）': 'price_range'
        })

        # 删除空行
        df = df.dropna(subset=['model'])

        # 导入数据库
        engine = create_engine(app.config['SQLALCHEMY_DATABASE_URI'])

        # 如果表不存在则创建
        db.create_all()

        # 批量插入数据
        df.to_sql(
            name='monthly_sales',
            con=engine,
            if_exists='append',
            index=False,
            chunksize=1000
        )

        print(f"成功导入 {len(df)} 条数据")


if __name__ == '__main__':
    import_excel_to_mysql('各汽车厂商各车型分月度销售量排名及售价数据2015-202309.xlsx')